<?php
include("../connection/connection.php");
function getSchemeNoDistWise($distId,$schemeName) {
if($schemeName!="HP") {
$sqlgetSchemeNoDistWise="SELECT count(*) FROM tbl_dtls_q1_main WHERE type_scheme='".$schemeName."' and district_id=".$distId;
}
else {
$sqlgetSchemeNoDistWise="SELECT count(*) FROM tbl_dtls_q2_main WHERE district_id=".$distId;
}
$querygetSchemeNoDistWise=mysql_query($sqlgetSchemeNoDistWise);
$arrCountSource=mysql_fetch_row($querygetSchemeNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getSchemeNoBlockWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetSchemeNoDistWise="SELECT count(*) FROM tbl_dtls_q1_main WHERE type_scheme='".$schemeName."' and district_id=".$distId." and block_id=".$blockId;
}
else {
$sqlgetSchemeNoDistWise="SELECT count(*) FROM tbl_dtls_q2_main WHERE district_id=".$distId." and block_id=".$blockId;;
}
$querygetSchemeNoDistWise=mysql_query($sqlgetSchemeNoDistWise);
$arrCountSource=mysql_fetch_row($querygetSchemeNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getPopulationSchemeWise($distId,$schemeName) {
if($schemeName!="HP") {
$sqlgetSchemeNoDistWise="SELECT sum(present_population) FROM tbl_dtls_q1_main where district_id=".$distId." and type_scheme='".$schemeName."'";
}
else {
$sqlgetSchemeNoDistWise="SELECT sum(present_population) FROM tbl_dtls_q2_main where district_id=".$distId;
}
$querygetSchemeNoDistWise=mysql_query($sqlgetSchemeNoDistWise);
$arrCountSource=mysql_fetch_row($querygetSchemeNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}


// Total Population Block Wise
function getPopulationPresenDistWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetPopulationDistWise="SELECT sum(present_population) FROM tbl_dtls_q1_main where district_id=".$distId." and block_id=".$blockId;
}
else {
$sqlgetPopulationDistWise="SELECT sum(present_population) FROM tbl_dtls_q2_main where district_id=".$distId." and block_id=".blockId;
}
$querygetPopulationDistWise=mysql_query($sqlgetPopulationDistWise);
$arrCountSource=mysql_fetch_row($querygetPopulationDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}


//Population Scheme block wise
function getPopulationSchemeBlockWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetSchemeNoDistWise="SELECT sum(present_population) FROM tbl_dtls_q1_main where district_id=".$distId." and block_id=".$blockId." and type_scheme='".$schemeName."'";
}
else {
$sqlgetSchemeNoDistWise="SELECT sum(present_population) FROM tbl_dtls_q2_main where district_id=".$distId." and block_id=".$blockId;
}
$querygetSchemeNoDistWise=mysql_query($sqlgetSchemeNoDistWise);
$arrCountSource=mysql_fetch_row($querygetSchemeNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}


//Population Tanker Block Wise
function getPopulationTankerBlockWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetTankerNoDistWise="SELECT sum(present_population) FROM tbl_dtls_q1_main where district_id=".$distId." and block_id=".$blockId." and type_scheme='".$schemeName."'";
}
else {
$sqlgetTankerNoDistWise="SELECT sum(main.present_population) FROM tbl_dtls_q2_main main, tbl_dtls_q2_tanker tank WHERE main.id=tank.q2_id and main.district_id= $distId and main.block_id =$blockId and tank.total_tanker_no>0";
//echo $sqlgetTankerNoDistWise;
}
$querygetTankerNoDistWise=mysql_query($sqlgetTankerNoDistWise);
$arrCountSource=mysql_fetch_row($querygetTankerNoDistWise);
return $arrCountSource[0];

//echo $sqlgetSchemeNoDistWise;
}

//Tanker No Block wise
function getTankerNoBlockWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetTankerNoDistWise="SELECT sum(tank.total_tanker_no) FROM tbl_dtls_q1_main main,tbl_dtls_q1_tanker tank WHERE main.id=tank.loc_id and district_id=".$distId." and block_id=".$blockId." and tank.total_tanker_no>0 and type_scheme='".$schemeName."'";
}
else {
$sqlgetTankerNoDistWise="SELECT sum(tank.total_tanker_no) FROM tbl_dtls_q2_main main, tbl_dtls_q2_tanker tank WHERE main.id=tank.q2_id and main.district_id= $distId and main.block_id =$blockId and tank.total_tanker_no>0";
//echo $sqlgetTankerNoDistWise;
}
$querygetTankerNoDistWise=mysql_query($sqlgetTankerNoDistWise);
$arrCountSource=mysql_fetch_row($querygetTankerNoDistWise);
return $arrCountSource[0];

//echo $sqlgetSchemeNoDistWise;
}

//Water Quantity Block wise
function getWaterQtyBlockWise($distId,$schemeName,$blockId) {
if($schemeName!="HP") {
$sqlgetWaterQtyDistWise="SELECT sum(tank.water_quantity) FROM tbl_dtls_q1_main main,tbl_dtls_q1_tanker tank WHERE main.id=tank.loc_id and district_id=".$distId." and block_id=".$blockId." and tank.water_quantity>0 and type_scheme='".$schemeName."'";
}
else {
$sqlgetWaterQtyDistWise="SELECT sum(tank.water_quantity) FROM tbl_dtls_q2_main main, tbl_dtls_q2_tanker tank WHERE main.id=tank.q2_id and main.district_id= $distId and main.block_id =$blockId and tank.water_quantity>0";
//echo $sqlgetTankerNoDistWise;
}
$querygetWaterQtyDistWise=mysql_query($sqlgetWaterQtyDistWise);
$arrCountSource=mysql_fetch_row($querygetWaterQtyDistWise);
return $arrCountSource[0];

//echo $sqlgetSchemeNoDistWise;
}

function getNumVillBlockWise($distId,$blockId) {
$sqlNumVillageQ1="SELECT count(village_id)as NumVill FROM `tbl_dtls_q1_main` where district_id=".$distId." and block_id=".$blockId;
$queryNumVillageQ1=mysql_query($sqlNumVillageQ1);
$arrsqlNumVillageQ1=mysql_fetch_row($queryNumVillageQ1);
$numVillQ1=$arrsqlNumVillageQ1[0];
$sqlNumVillageQ2="SELECT count(village_id)as NumVill FROM `tbl_dtls_q2_main` where district_id=".$distId." and block_id=".$blockId;
$queryNumVillageQ2=mysql_query($sqlNumVillageQ2);
$arrsqlNumVillageQ2=mysql_fetch_row($queryNumVillageQ2);
$numVillQ2=$arrsqlNumVillageQ2[0];

$totalNumVill=$numVillQ1 + $numVillQ2;
return $totalNumVill;
//echo $sqlgetSchemeNoDistWise;
}




/*getSchemeNoDistWise(418,'P&T');
echo "<br>";
getSchemeNoBlockWise(418,'P&T',4311);
echo "<br>";
getPopulationSchemeWise(418,'P&T');
echo "<br>";
getPopulationSchemeBlockWise(418,'P&T',4311); */

//function for report4
function getReservoirNoSchemeWise($distId,$reservoirName,$total) {
echo "Total:".$total;
$sqlgetReservoirNoSchemeWise="SELECT sum($total) FROM tbl_dtls_q1_main WHERE type_scheme=".$reservoirName." and district_id=".$distId;
echo $sqlgetReservoirNoSchemeWise;
$querygetReservoirNoSchemeWise=mysql_query($sqlgetReservoirNoSchemeWise);
$arrCountReservoir=mysql_fetch_row($querygetReservoirNoSchemeWise);
return $arrCountReservoir[0];
//echo $sqlgetSchemeNoDistWise;
}


function getSupplyDuration($distId,$minDuration,$maxDuration)
{
	$sqlgetSupplyDuration="SELECT count(`duration_supply`) FROM `tbl_dtls_q1_last` where loc_id =(select distinct loc_id from tbl_dtls_q1_main where district_id=".$distId.")";
	if($minDuration!="" && $minDuration!=="zero") {$sqlgetSupplyDuration.=" and `duration_supply`>".$minDuration; }
	if($minDuration==="zero") {$sqlgetSupplyDuration.=" and `duration_supply`=0 and `duration_supply`!=''"; }
	if($maxDuration!=="" && $maxDuration!=="null" ) {$sqlgetSupplyDuration.=" and `duration_supply`<=".$maxDuration; }
	if($maxDuration=="null" ) {$sqlgetSupplyDuration.=" and `duration_supply`=''"; }
	//echo $sqlgetSupplyDuration;
	$querygetSupplyDuration=mysql_query($sqlgetSupplyDuration);
	$arrSupplyDuration=mysql_fetch_row($querygetSupplyDuration);
	return $arrSupplyDuration[0];
}


//Time Taken
function getTimeTaken($distId,$blockId,$minDuration,$maxDuration)
{
	$sqlgetSupplyDuration="SELECT count(time_taken) from tbl_dtls_q1_main main,tbl_dtls_q1_last last where district_id=".$distId." and main.id=last.loc_id and block_id=".$blockId;
	if($minDuration!="" && $minDuration!=="zero") {$sqlgetSupplyDuration.=" and `time_taken`>".$minDuration; }
	if($minDuration==="zero") {$sqlgetSupplyDuration.=" and `time_taken`=0 and `time_taken`!=''"; }
	if($maxDuration!=="" && $maxDuration!=="null" ) {$sqlgetSupplyDuration.=" and `time_taken`<=".$maxDuration; }
	if($maxDuration=="null" ) {$sqlgetSupplyDuration.=" and `time_taken`='Not-Provided'"; }
	//echo $sqlgetSupplyDuration;
	$querygetTimeTaken=mysql_query($sqlgetSupplyDuration);
	$rowgetTimeTaken=mysql_fetch_row($querygetTimeTaken);
	return $rowgetTimeTaken[0];
}

function getName($colName,$id)
{
	//$arrColName=explode('_',$colName);
	//$colNameText=$arrColName[0]."_name";
	$sqlgetName="select distinct ".$colName."_name from tbl_lookup_location where ".$colName."_id=".$id;
	$querygetName=mysql_query($sqlgetName);
	$rowgetName=mysql_fetch_row($querygetName);
	return $rowgetName[0];
}

function getStorageSchemeWise($distId,$typeId,$reservId)
{
	$sqlStorage="Select sum(reserv_cap) FROM `tbl_dtls_q1_reserv` WHERE `loc_id` IN (SELECT DISTINCT id FROM tbl_dtls_q1_main WHERE district_id =".$distId." AND  `type_scheme` = '".$typeId."') AND `reserv_id` LIKE '".$reservId."%'";
	$queryStorage=mysql_query($sqlStorage);
	$rowStorage=mysql_fetch_row($queryStorage);
	return $rowStorage[0];
}

function getPopulationStorageWise($distId,$typeId,$reservId)
{
	if($reservId=="blank") {
	$sqlStorage="SELECT sum( present_population ) FROM tbl_dtls_q1_main WHERE district_id =".$distId." AND type_scheme = '".$typeId."' AND (cwr_total=0 or glsr_total=0 or glr_total=0 or ohsr_total=0)";
	}
	else {
	$sqlStorage="SELECT sum( present_population ) FROM tbl_dtls_q1_main WHERE district_id =".$distId." AND type_scheme = '".$typeId."' AND ".$reservId."_total >0";
	}
	//Select sum(reserv_cap) FROM `tbl_dtls_q1_reserv` WHERE `loc_id` IN (SELECT DISTINCT id FROM tbl_dtls_q1_main WHERE district_id =".$distId." AND  `type_scheme` = '".$typeId."') AND `reserv_id` LIKE '".$reservId."%'";
	$queryStorage=mysql_query($sqlStorage);
	$rowStorage=mysql_fetch_row($queryStorage);
	return $rowStorage[0];
}

function getBillRaise($distId,$schemeName,$blockId) {
$sqlgetBillRaise="select sum(`wbs_9_10`),sum(`wbs_8_9`),sum(`wbs_7_8`),sum(`rg_9_10`),sum(`rg_8_9`),sum(`rg_7_8`) from tbl_dtls_q1_main main,tbl_dtls_q1_last last where main.id=last.loc_id and district_id=".$distId." and block_id=".$blockId." and type_scheme='".$schemeName."'";
//echo $sqlgetBillRaise;
$querygetBillRaise=mysql_query($sqlgetBillRaise);
$rowgetBillRaise=mysql_fetch_row($querygetBillRaise);
return $rowgetBillRaise;
}
//////////////////////
function getBill_water_Raise($distId,$schemeName,$blockId) {
$getBill_water_Raise="select sum(`water_supply`),sum(`wbs_8_9`),sum(`rg_8_9`),sum(`mpc_8_9`)+sum(`mc_mr_8_9`)+sum(`mc_pr_8_9`)+sum(`pc_8_9`)+sum(`cc_8_9`) as omCost from tbl_dtls_q1_main main,tbl_dtls_q1_last last where main.id=last.loc_id and district_id=".$distId." and block_id=".$blockId." and type_scheme='".$schemeName."'";
//echo $getBill_water_Raise;
$querygetBill_water_Raise=mysql_query($getBill_water_Raise);
$rowgetBillRaise=mysql_fetch_row($querygetBill_water_Raise);
return $rowgetBillRaise;
}
///////////////////////


//Girish Function
function getManPowerBlockWise($blockId,$cost) 
{
$sqlgetManPowerBlockWise="SELECT sum($cost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise=mysql_query($sqlgetManPowerBlockWise);
$result=mysql_fetch_row($querygetManPowerBlockWise);
return $result[0];
}
function getSumManPowerBlockWise($distid,$cost) 
{
$sqlgetManPowerBlockWise="SELECT sum($cost) FROM `tbl_dtls_q2_main` where district_id=".$distid;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise=mysql_query($sqlgetManPowerBlockWise);
$result=mysql_fetch_row($querygetManPowerBlockWise);
return $result[0];
}
function getOMPowerBlockWise($blockId,$mancost,$matcost) 
{
$sqlgetManPowerBlockWise1="SELECT sum($mancost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise1=mysql_query($sqlgetManPowerBlockWise1);
$result1=mysql_fetch_row($querygetManPowerBlockWise1);
//return $result1[0];
$sqlgetManPowerBlockWise2="SELECT sum($matcost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise2=mysql_query($sqlgetManPowerBlockWise2);
$result2=mysql_fetch_row($querygetManPowerBlockWise2);
//return $result2[0];
return $c=$result1[0]+$result2[0];
}
function getSumOMPowerBlockWise($distId,$mancost,$matcost) 
{
$sqlgetManPowerBlockWise1="SELECT sum($mancost) FROM `tbl_dtls_q2_main` where district_id=".$distId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise1=mysql_query($sqlgetManPowerBlockWise1);
$result1=mysql_fetch_row($querygetManPowerBlockWise1);
//return $result1[0];
$sqlgetManPowerBlockWise2="SELECT sum($matcost) FROM `tbl_dtls_q2_main` where district_id=".$distId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise2=mysql_query($sqlgetManPowerBlockWise2);
$result2=mysql_fetch_row($querygetManPowerBlockWise2);
//return $result2[0];
return $c=$result1[0]+$result2[0];
}
function getTotalHP($block)
{
$q4="SELECT COUNT(block_id) FROM `tbl_dtls_q2_main` WHERE 
block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_name='$block') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
function getSumTotalHP($distId)
{
$q4="SELECT COUNT(district_id) FROM `tbl_dtls_q2_main` WHERE 
district_id=(select DISTINCT district_id FROM tbl_lookup_location where district_id='$distId') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
function getManDevideHP($block,$blockId,$cost)
{
$sqlgetManPowerBlockWise="SELECT sum($cost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise=mysql_query($sqlgetManPowerBlockWise);
$result=mysql_fetch_row($querygetManPowerBlockWise);
//return $result[0];
$q4="SELECT COUNT(block_id) FROM `tbl_dtls_q2_main` WHERE 
block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_name='$block') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
return $a=number_format(@($result[0]/$x[0]),2);
}
function getSumManDevideHP($distId,$cost)
{
$sqlgetManPowerBlockWise="SELECT sum($cost) FROM `tbl_dtls_q2_main` where district_id=".$distId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise=mysql_query($sqlgetManPowerBlockWise);
$result=mysql_fetch_row($querygetManPowerBlockWise);
//return $result[0];
$q4="SELECT COUNT(district_id) FROM `tbl_dtls_q2_main` WHERE 
district_id=(select DISTINCT district_id FROM tbl_lookup_location where district_id='$distId') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
return $a=number_format(@($result[0]/$x[0]),2);
}
function getOManDevideHP($block,$blockId,$mancost,$matcost)
{
$sqlgetManPowerBlockWise1="SELECT sum($mancost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise1=mysql_query($sqlgetManPowerBlockWise1);
$result1=mysql_fetch_row($querygetManPowerBlockWise1);
//return $result1[0];
$sqlgetManPowerBlockWise2="SELECT sum($matcost) FROM `tbl_dtls_q2_main` where block_id=".$blockId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise2=mysql_query($sqlgetManPowerBlockWise2);
$result2=mysql_fetch_row($querygetManPowerBlockWise2);
//return $result2[0];
$c=$result1[0]+$result2[0];
$q4="SELECT COUNT(block_id) FROM `tbl_dtls_q2_main` WHERE 
block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_name='$block') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $w=number_format(@($c/$x[0]),2);
}
function getSumOManDevideHP($distId,$mancost,$matcost)
{
$sqlgetManPowerBlockWise1="SELECT sum($mancost) FROM `tbl_dtls_q2_main` where district_id=".$distId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise1=mysql_query($sqlgetManPowerBlockWise1);
$result1=mysql_fetch_row($querygetManPowerBlockWise1);
//return $result1[0];
$sqlgetManPowerBlockWise2="SELECT sum($matcost) FROM `tbl_dtls_q2_main` where district_id=".$distId;
//echo $sqlgetManPowerBlockWise;
$querygetManPowerBlockWise2=mysql_query($sqlgetManPowerBlockWise2);
$result2=mysql_fetch_row($querygetManPowerBlockWise2);
//return $result2[0];
$c=$result1[0]+$result2[0];
$q4="SELECT COUNT(district_id) FROM `tbl_dtls_q2_main` WHERE 
district_id=(select DISTINCT district_id FROM tbl_lookup_location where district_id='$distId') " ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $w=number_format(@($c/$x[0]),2);
}
function getTotalNotfunctional($blockId,$functionalstatus)
{
$q4="SELECT COUNT(functional_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and functional_status='$functionalstatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
//////////////////////////
function getTotalNotRepairable($blockId,$rpairablestatus)
{
$q4="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and functional_status='NOT FUNCTIONAL' and repairable_status='$rpairablestatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

/////////////////////////////
function getPercentNotRepairable($blockId,$rpairablestatus)
{
$q="SELECT COUNT(functional_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and functional_status='NOT FUNCTIONAL'" ;
$r=mysql_query($q);
$x=mysql_fetch_row($r);
//$x[0];

$q1="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and repairable_status='$rpairablestatus'" ;
$r1=mysql_query($q1);
$x1=mysql_fetch_row($r1);
//return $x1[0];
if($x[0]==0)
{
return "0.00";
}else{
return $q=number_format(($x1[0]/$x[0])*100,2);
}
}
function getRangeWater($blockId,$waterMinMax)
{
$q1="SELECT min($waterMinMax),max($waterMinMax) from `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId')" ;
$r1=mysql_query($q1);
$x1=mysql_fetch_row($r1);
if($x1[0]=="")
{
$x1[0]=0;
return $x1[0]."-".$x1[1];
}else{
return $x1[0]."-".$x1[1];
}
}
function getSumfunctionalStatus($distId,$functionalstatus)
{
$q4="SELECT COUNT(*) FROM tbl_dtls_q2_main a,tbl_dtls_q2_hp_status b WHERE 
b.q2_id =a.id and a.district_id='$distId' and b.functional_status='$functionalstatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
//////////////////////////
function getSumRepairableStatus($distId,$RPstatus)
{
$q4="SELECT COUNT(*) FROM tbl_dtls_q2_main a,tbl_dtls_q2_hp_status b WHERE 
b.q2_id =a.id and a.district_id='$distId' and b.functional_status='NOT FUNCTIONAL' and b.repairable_status='$RPstatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
////////////////////////////

function getSumPercentNotRepairable($distId,$rpairablestatus)
{
$q="SELECT COUNT(functional_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where district_id='$distId') and functional_status='NOT FUNCTIONAL'" ;
$r=mysql_query($q);
$x=mysql_fetch_row($r);
//$x[0];

$q1="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where district_id='$distId') and repairable_status='$rpairablestatus'" ;
$r1=mysql_query($q1);
$x1=mysql_fetch_row($r1);
//return $x1[0];
if($x[0]==0)
{
return "0.00";
}else{
return $q=number_format(($x1[0]/$x[0])*100,2);
}
}

function getpreventivepiped($blockId,$scheme)
{
$q4="SELECT count(preventive_place) FROM `tbl_dtls_q1_last` where preventive_place='yes' and loc_id in (select id from tbl_dtls_q1_main where type_scheme='$scheme' and block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_id='$blockId'))" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getpreventivehp($blockId)
{
$q4="SELECT count(maintenence_machanism) FROM `tbl_dtls_q2_main` where maintenence_machanism='yes' and block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_id='$blockId')" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
function getcomplaint_for_scheme($complaint,$blockId,$scheme)
{
$q1="SELECT count($complaint) FROM `tbl_dtls_q1_last` where loc_id in (select id from tbl_dtls_q1_main where type_scheme='$scheme' and block_id=(select DISTINCT block_id FROM tbl_lookup_location where block_id='$blockId')) and $complaint!=''";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}
///////////////////////////////////////////
function getTotalInstitutionalDistrictWise($constraint,$constraint1,$distId)
{
$q1="SELECT count($constraint) FROM tbl_dtls_q1_last where loc_id in(select id from tbl_dtls_q1_main where district_id=$distId) and $constraint!='' ";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);

$q2="SELECT count($constraint1) FROM tbl_dtls_q2_main where  district_id=$distId and $constraint1!='' ";
$r2=mysql_query($q2);
$x1=mysql_fetch_row($r2);

return $y=$x[0]+$x1[0];
}

///function for Reeoprt 15
function getSources($source,$blockId)
{
	$q1="SELECT count(a.source) FROM tbl_dtls_q1_sws a,tbl_dtls_q1_main b where a.loc_id=b.id and a.source='".$source."' and b.block_id='".$blockId."'";
	$r1=mysql_query($q1);
	$x=mysql_fetch_row($r1);
	return $x[0];
}
/*function getSources($source,$blockId)
{
	$q1="SELECT count(source) FROM `tbl_dtls_q1_sws` where source='".$source."' and loc_id in(select id from tbl_dtls_q1_main where block_id='".$blockId."')";
	$r1=mysql_query($q1);
	$x=mysql_fetch_row($r1);
	return $x[0];
}*/
function getSourcesBasedHP($hp,$source,$blockId)
{
$q1="select sum($hp) from tbl_dtls_q1_main a,tbl_dtls_q1_sws b where a.id=b.loc_id and b.source='$source' and a.block_id='".$blockId."'";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}
/*function getSourcesBasedHP($hp,$source,$blockId)
{
$q1="select sum($hp) from tbl_dtls_q1_main where id in(select loc_id from tbl_dtls_q1_sws where source='$source') and block_id='".$blockId."'";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}*/
/*function getMaximumYeild($source,$blockId)
{
$q1="SELECT sum(yield_max) FROM tbl_dtls_q1_sws where loc_id in(select id from tbl_dtls_q1_main where block_id='".$blockId."') and source='".$source."'";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}*/
function getMaximumYeild($source,$blockId)
{
$q1="SELECT sum(a.yield_max) FROM tbl_dtls_q1_sws a,tbl_dtls_q1_main b where a.loc_id=b.id and b.block_id='".$blockId."' and a.source='".$source."'";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}
//end report 15
///function for report 18 start
function getTotalHabitationWise($field,$habitation,$blockId)
{
$q1="SELECT count($field) FROM `tbl_dtls_q2_habit` where q2_id in(select id FROM tbl_dtls_q2_main where block_id='".$blockId."') and type_mh_oh='".$habitation."' ";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}
function getSumHabitationWise($field,$habitation,$blockId)
{
$q1="SELECT sum($field) FROM `tbl_dtls_q2_habit` where q2_id in(select id FROM tbl_dtls_q2_main where block_id='".$blockId."') and type_mh_oh='".$habitation."' ";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}
function getDPpopulation($field,$blockId)
{
$q1="SELECT sum($field) FROM `tbl_dtls_q2_main` where block_id='".$blockId."' ";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
return $x[0];
}

//report 18 end

//Function for report 19 start
function getcur_population_for_scheme($blockId,$scheme)
{
$q1="SELECT sum(present_population) FROM `tbl_dtls_q1_main` where type_scheme='$scheme' and block_id='$blockId'";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
if($x[0]=="")
{
return 0;
}else{
return $x[0];
}
}
function getWater_Supplied_domesticNo_for_scheme($arg,$blockId,$scheme)
{
$q1="SELECT sum($arg) FROM `tbl_dtls_q1_last` where loc_id in(select id from tbl_dtls_q1_main where type_scheme='$scheme' and block_id='$blockId')";
$r1=mysql_query($q1);
$x=mysql_fetch_row($r1);
if($x[0]=="")
{
return 0;
}else{
return $x[0];
}
}
//end of report 19



//shakil ahmad
//========================================================================================================


function getFunctionalStatusNoDistWise($distId,$statusName){

	$sqlgetstatusNoDistWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;


$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getFunctionalStatusNoBlockWise($distId,$statusName,$blockId){

	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getOperationalStatusNoDistWise($distId,$statusName){

	$sqlgetopstatusNoDistWise="SELECT count(b.operational_status ) as operational_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.operational_status = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;


$querygetopstatusNoDistWise=mysql_query($sqlgetopstatusNoDistWise);
$arrCount=mysql_fetch_row($querygetopstatusNoDistWise);
return $arrCount[0];
//echo $sqlgetSchemeNoDistWise;
}

function getOperationalStatusNoBlockWise($distId,$statusName,$blockId){

	$sqlgetopstatusNoBlockWise="SELECT count(b.operational_status ) as operational_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.operational_status = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetopstatusNoBlockWise=mysql_query($sqlgetopstatusNoBlockWise);
$arrCount=mysql_fetch_row($querygetopstatusNoBlockWise);
return $arrCount[0];
//echo $sqlgetSchemeNoDistWise;
}

function percentoperationalDistWise($distId,$statusName,$a)
{
	$sqlgetstatusNoDistWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;
$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
//return $arrCountSource[0];

$sqlgetopstatusNoDistWise="SELECT count(b.operational_status ) as operational_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.operational_status = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;
$querygetopstatusNoDistWise=mysql_query($sqlgetopstatusNoDistWise);
$arrCount=mysql_fetch_row($querygetopstatusNoDistWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function percentoperational($distId,$statusName,$a,$blockId)
{
	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];

$sqlgetopstatusNoBlockWise="SELECT count(b.operational_status ) as operational_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.operational_status = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetopstatusNoBlockWise=mysql_query($sqlgetopstatusNoBlockWise);
$arrCount=mysql_fetch_row($querygetopstatusNoBlockWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function getPumpWaterDistWise($distId,$a)
{
$sql="select count(*) from tbl_dtls_q2_main where OA_operational_status=".$a." and district_id=".$distId;
$query=mysql_query($sql);
$arrCount=mysql_fetch_row($query);
return $arrCount[0];


}

function getPumpWater($distId,$a,$blockId)
{
$sql="select count(*) from tbl_dtls_q2_main where OA_operational_status=".$a." and district_id=".$distId." and block_id=".$blockId;
$query=mysql_query($sql);
$arrCount=mysql_fetch_row($query);
return $arrCount[0];


}

function getPumpWaterDistWiseother($distId,$a,$b,$c)
{
$sql="select count(*) from tbl_dtls_q2_main where OA_operational_status!=".$a." and OA_operational_status!=".$b." and OA_operational_status!=".$c." and district_id=".$distId;
$query=mysql_query($sql);
$arrCount=mysql_fetch_row($query);
return $arrCount[0];


}

function getPumpWaterother($distId,$a,$b,$c,$blockId)
{
$sql="select count(*) from tbl_dtls_q2_main where OA_operational_status!=".$a." and OA_operational_status!=".$b." and OA_operational_status!=".$c." and district_id=".$distId." and block_id=".$blockId;
$query=mysql_query($sql);
$arrCount=mysql_fetch_row($query);
return $arrCount[0];

}

function getQualityStatusNoDistWise($distId,$statusName){

	$sqlgetstatusNoDistWise="SELECT count( b.quality_status ) as quality_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.quality_status = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;


$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getQualityStatusNoBlockWise($distId,$statusName,$blockId){

	$sqlgetstatusNoBlockWise="SELECT count( b.quality_status ) as quality_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.quality_status = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}


function percentQualityDistWise($distId,$statusName,$a)
{
	$sqlgetstatusNoDistWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;
$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
//return $arrCountSource[0];

$sqlgetopstatusNoDistWise="SELECT count(b.quality_status ) as quality_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.quality_status = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;
$querygetopstatusNoDistWise=mysql_query($sqlgetopstatusNoDistWise);
$arrCount=mysql_fetch_row($querygetopstatusNoDistWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function percentQualityBlockWise($distId,$statusName,$a,$blockId)
{
	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];

$sqlgetopstatusNoBlockWise="SELECT count(b.quality_status ) as quality_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.quality_status = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetopstatusNoBlockWise=mysql_query($sqlgetopstatusNoBlockWise);
$arrCount=mysql_fetch_row($querygetopstatusNoBlockWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function getYieldStatusNoDistWise($distId,$statusName){

	$sqlgetstatusNoDistWise="SELECT count( b.yield ) as yield FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.yield = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;


$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getYieldStatusNoBlockWise($distId,$statusName,$blockId){

	$sqlgetstatusNoBlockWise="SELECT count( b.yield ) as yield FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.yield = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function percentYieldDistWise($distId,$statusName,$a)
{
	$sqlgetstatusNoDistWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;
$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
//return $arrCountSource[0];

$sqlgetopstatusNoDistWise="SELECT count(b.yield ) as yield FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.yield = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;
$querygetopstatusNoDistWise=mysql_query($sqlgetopstatusNoDistWise);
$arrCount=mysql_fetch_row($querygetopstatusNoDistWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0"; 
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function percentYieldBlockWise($distId,$statusName,$a,$blockId)
{
	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];

$sqlgetopstatusNoBlockWise="SELECT count(b.yield ) as yield FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.yield = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetopstatusNoBlockWise=mysql_query($sqlgetopstatusNoBlockWise);
$arrCount=mysql_fetch_row($querygetopstatusNoBlockWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2,'.','');}


}

function getSeasonalFluctuationNoDistWise($distId,$statusName){

	$sqlgetstatusNoDistWise="SELECT count( b.seasonal_fluctuation ) as seasonal_fluctuation FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.seasonal_fluctuation = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;


$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function getSeasonalFluctuationNoBlockWise($distId,$statusName,$blockId){

	$sqlgetstatusNoBlockWise="SELECT count( b.seasonal_fluctuation ) as seasonal_fluctuation FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.seasonal_fluctuation = '".$statusName."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
}

function percentSeasonalFluctuationDistWise($distId,$statusName,$a)
{
	$sqlgetstatusNoDistWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;
$querygetstatusNoDistWise=mysql_query($sqlgetstatusNoDistWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoDistWise);
//return $arrCountSource[0];

$sqlgetopstatusNoDistWise="SELECT count(b.seasonal_fluctuation ) as seasonal_fluctuation FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.seasonal_fluctuation = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId;
$querygetopstatusNoDistWise=mysql_query($sqlgetopstatusNoDistWise);
$arrCount=mysql_fetch_row($querygetopstatusNoDistWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

function percentSeasonalFluctuationBlockWise($distId,$statusName,$a,$blockId)
{
	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];

$sqlgetopstatusNoBlockWise="SELECT count(b.seasonal_fluctuation ) as seasonal_fluctuation FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.seasonal_fluctuation = '".$a."' AND b.functional_status='FUNCTIONAL' AND a.district_id =".$distId." AND a.block_id=".$blockId;
$querygetopstatusNoBlockWise=mysql_query($sqlgetopstatusNoBlockWise);
$arrCount=mysql_fetch_row($querygetopstatusNoBlockWise);
//return $arrCount[0];
if($arrCountSource[0]==0)
{
return $a="0.00";
}else{
return $a=number_format(($arrCount[0]/$arrCountSource[0])*100,2);
}

}

//Md shakil ahmad 4/9/2010
function getNotFunctionalNoBlockWise($distId,$status,$blockId)
{
$q4="SELECT COUNT(repairable_status) FROM tbl_dtls_q2_main a,tbl_dtls_q2_hp_status b WHERE 
b.q2_id =a.id and a.district_id='$distId' and b.functional_status='NOT FUNCTIONAL' and b.repairable_status!='$status' and a.block_id='$blockId'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getNotFunctionalNoDistWise($distId,$status)
{
$q4="SELECT COUNT(repairable_status) FROM tbl_dtls_q2_main a,tbl_dtls_q2_hp_status b WHERE 
b.q2_id =a.id and a.district_id='$distId' and b.functional_status='NOT FUNCTIONAL' and b.repairable_status!='$status'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getTotalFunctionalStatusNoBlockWise($distId,$blockId)
{
$q4="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId' and a.block_id='$blockId'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getTotalFunctionalStatusNoDistWise($distId)
{
$q4="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}



function getPercentFunctionalNoBlockWise($distId,$statusName,$rpairablestatus,$blockId){

	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId." AND a.block_id=".$blockId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
$q4="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and functional_status='NOT FUNCTIONAL' and repairable_status='$rpairablestatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
$q5="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId' and a.block_id='$blockId'" ;
$r5=mysql_query($q5);
$y=mysql_fetch_row($r5);
//return $x[0];
if($y[0]==0)
{
return $a="0";
}else{
return $a=number_format((($arrCountSource[0]+$x[0])/$y[0])*100,2);
}
}

function getPercentFunctionalNoDistWise($distId,$statusName,$rpairablestatus){

	$sqlgetstatusNoBlockWise="SELECT count( b.functional_status ) as functional_status FROM tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b WHERE a.id = b.q2_id AND b.functional_status = '".$statusName."' AND a.district_id =".$distId;


$querygetstatusNoBlockWise=mysql_query($sqlgetstatusNoBlockWise);
$arrCountSource=mysql_fetch_row($querygetstatusNoBlockWise);
//return $arrCountSource[0];
//echo $sqlgetSchemeNoDistWise;
$q4="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where district_id=$distId) and functional_status='NOT FUNCTIONAL' and repairable_status='$rpairablestatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
$q5="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId'" ;
$r5=mysql_query($q5);
$y=mysql_fetch_row($r5);
//return $x[0];
if($y[0]==0)
{
return $a="0";
}else{
return $a=number_format((($arrCountSource[0]+$x[0])/$y[0])*100,2);
}
}

function getPercentNonFunctionalNoBlockWise($distId,$rpairablestatus,$blockId){

	
$q4="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where block_id='$blockId') and functional_status='NOT FUNCTIONAL' and repairable_status='$rpairablestatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
$q5="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId' and a.block_id='$blockId'" ;
$r5=mysql_query($q5);
$y=mysql_fetch_row($r5);
//return $x[0];
if($y[0]==0)
{
return $a="0";
}else{
return $a=number_format(($x[0]/$y[0])*100,2);
}
}

function getPercentNonFunctionalNoDistWise($distId,$rpairablestatus){

	
$q4="SELECT COUNT(repairable_status) FROM `tbl_dtls_q2_hp_status` WHERE 
q2_id in(select id FROM tbl_dtls_q2_main where district_id='$distId') and functional_status='NOT FUNCTIONAL' and repairable_status='$rpairablestatus'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
//return $x[0];
$q5="select count(b.functional_status) from tbl_dtls_q2_main a, tbl_dtls_q2_hp_status b where a.id=b.q2_id and a.district_id='$distId'" ;
$r5=mysql_query($q5);
$y=mysql_fetch_row($r5);
//return $x[0];
if($y[0]==0)
{
return $a="0";
}else{
return $a=number_format(($x[0]/$y[0])*100,2,'.','');
}
}
function getTubeOpenWellSource($distId,$source,$scheme,$curstatus)
{
$q4="select count(b.current_status) from tbl_dtls_q1_main a,tbl_dtls_q1_sws b where a.id=b.loc_id and a.type_scheme='$scheme' and b.source='$source' and b.current_status='$curstatus' and a.district_id='$distId'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getPumpingInformation($blockId,$scheme)
{
$q4="SELECT sum(sub_total),sum(mono_total),sum(centi_total),sum(sp_total) FROM `tbl_dtls_q1_main` where type_scheme='$scheme' and block_id='$blockId'" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $y=$x[0]+$x[1]+$x[2]+$x[3];
}

function getPumpingStatusInformation($blockId,$asset_status,$scheme)
{
$q4="SELECT count(asset_status) FROM `tbl_dtls_q1_asset` where asset_status='$asset_status' and loc_id in(select id from tbl_dtls_q1_main where type_scheme='$scheme' and block_id='$blockId')" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}

function getPowerRatingInformation($blockId,$scheme)
{
$q4="SELECT sum(asset_power) FROM `tbl_dtls_q1_asset` where loc_id in(select id from tbl_dtls_q1_main where type_scheme='$scheme' and block_id='$blockId')" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}
function getHPInformation($blockId)
{
$q4="SELECT sum(hp_no) FROM `tbl_dtls_q2_habit` where q2_id in(select id from tbl_dtls_q2_main where block_id='".$blockId."')" ;
$r4=mysql_query($q4);
$x=mysql_fetch_row($r4);
return $x[0];
}



?>